﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using SBS;

namespace Data
{
    class Mekaniker_DB
    {
        public static string ConnectionString
        {
            get { return @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SBSdb.mdf;Integrated Security=True;User Instance=True"; }
        }
        /// <summary>
        /// Henter liste over mekanikere der har valgte certifikat
        /// </summary>
        /// <param name="CertNr"></param>
        /// <returns>Dictionary af Mekanikere</returns>
        public static Dictionary<int, Mekaniker> HentMekanikere(int CertNr)
        {
            Dictionary<int, Mekaniker> mekanikere = new Dictionary<int, Mekaniker>();

            string sql = "SELECT * FROM MekanikerCert WHERE Certnr = "+CertNr;
            SqlConnection conn = new SqlConnection(ConnectionString);
            try
            {
                conn.Open();
                SqlCommand com = new SqlCommand(sql,conn);
                SqlDataReader reader = com.ExecuteReader();

                while (reader.Read())
                {
                    mekanikere.Add(int.Parse(reader[0].ToString()), new Mekaniker(int.Parse(reader[0].ToString()), reader[1].ToString(), int.Parse(reader[3].ToString()), reader[2].ToString()));
                }

                return mekanikere;
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                conn.Close();
            }

        }

        public static List<ArbejdsOpgaver> HentMekanikerKalender(int MekNr)
        {
            DateTime fra = DateTime.Now;
            DateTime til = fra.AddDays(7);
            List<ArbejdsOpgaver> liste = new List<ArbejdsOpgaver>();

            string sql = "SELECT SUM(TID),COUNT(Onr),Udforsdato FROM Mekaniker_Kalender WHERE Meknr = " + MekNr + " AND Udforsdato > CONVERT(DATETIME, '" + fra.Date.ToString() +"',104) AND Udforsdato <= CONVERT(DATETIME, '" + til.Date.ToString() +"',104) GROUP BY Udforsdato";

            SqlConnection conn = new SqlConnection(ConnectionString);
            try
            {
                

                conn.Open();
                SqlCommand com = new SqlCommand(sql, conn);

                SqlDataReader reader = com.ExecuteReader();

                while (reader.Read())
                {
                    if (!reader.IsDBNull(0))
                    {
                        ArbejdsOpgaver m = new ArbejdsOpgaver(DateTime.Parse(reader[2].ToString()), int.Parse(reader[1].ToString()), TimeSpan.FromMinutes(double.Parse(reader[0].ToString())));
                        liste.Add(m);
                    }
                }

                return liste;
            }

            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                conn.Close();
            }

        }

        public static List<KeyValuePair<int,string>> HentAlleMekanikere()
        {
            List<KeyValuePair<int, string>> mekanikere = new List<KeyValuePair<int, string>>();

            string sql = "SELECT * FROM Mekaniker";
            SqlConnection conn = new SqlConnection(ConnectionString);
            try
            {
                conn.Open();
                SqlCommand com = new SqlCommand(sql, conn);
                SqlDataReader reader = com.ExecuteReader();

                while (reader.Read())
                {
                    mekanikere.Add(new KeyValuePair<int, string>(int.Parse(reader[0].ToString()), reader[1].ToString()));
                }

                return mekanikere;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                conn.Close();
            }

        }
    }
}
